import mysql.connector


def subject_sort(subjects):
    order = {"物理": 1, "历史": 2, "化学": 3, "政治": 4, "生物": 5, "地理": 6, "技术": 7}
    if not all(subject in order.keys() for subject in subjects):
        raise ValueError()
    return sorted(subjects, key=lambda x: order.get(x, float('inf')))


def index_sim_search(province_name, choose_list, rank, type="高风险", school_province_name="",
                     is_double_first_class=False, is_985=False, is_211=False):
    con = mysql.connector.connect(
        host="192.168.80.190",
        port=3306,
        user="hive",
        password="12345678",
        database="gaokao"
    )
    cursor = con.cursor()
    if type == "高风险":
        rank_min = int(max(rank - 20000 - rank * 0.1, 0))
        rank_max = rank
    elif type == "中风险":
        rank_min = int(max(rank - 10000 - rank * 0.1, 0))
        rank_max = int(rank + 10000 + rank * 0.1)
    else:
        rank_min = int(rank + rank * 0.1)
        rank_max = int(rank + 20000 + rank * 0.1)
    columns = ["school_name", "min_rank", "choose1", "choose2", "choose3"]
    choose_list = subject_sort(choose_list)
    rank_where = "min_rank >= {} and min_rank <= {}".format(rank_min, rank_max)
    where = []
    where.append("province_name = '{}'".format(province_name))
    if school_province_name != "" and school_province_name != "全部":
        where.append("school_province_name = '{}'".format(school_province_name))
    if is_double_first_class:
        where.append("is_dual_class = 1")
    if is_985:
        where.append("is_985 = 1")
    if is_211:
        where.append("is_211 = 1")
    if len(where) > 0:
        where = rank_where + " and " + " and ".join(where)
    else:
        where = rank_where
    q1 = "select {} from grade_sim_index where {}".format(",".join(columns), where)
    where = []
    where.append("choose1 is null or choose1=''")
    where.append("(choose1 = '{}' and (choose2 is null or choose2=''))".format(choose_list[2]))
    where.append(
        "(choose1 = '{}' and "
        "(choose2 is null or choose2='' or choose2='{}'))".format(choose_list[1], choose_list[2]))
    where.append("(choose1 = '{}' and "
                 "(choose2 is null or choose2='' or "
                 "(choose2='{}' and "
                 "(choose3 is null or choose3='' or choose3='{}')) or "
                 "(choose2='{}' and (choose3 is null or choose3=''))))".format(choose_list[0], choose_list[1],
                                                                               choose_list[2], choose_list[2]))

    q2 = "select school_name, avg(min_rank) as m from ({}) as t1 where {} GROUP BY school_name". \
        format(q1, " or ".join(where))
    q = "select * from ({}) as t2 order by abs(m-{}) LIMIT 30".format(q2, rank)
    cursor.execute(q)
    results = cursor.fetchall()
    print(results)
    cursor.close()
    con.close()


index_sim_search("内蒙古", ["物理", "生物", "化学"], 2000000, "高风险", "", is_985=True)
